# coding:utf8
import pymysql
import yaml
from common.Logger import logger


class Mysql:
    # 处理.sql备份文件为SQL语句
    def __read_sql_file(self,file_path):
        # 打开SQL文件到f
        sql_list = []
        with open(file_path, 'r', encoding='utf8') as f:
            # 逐行读取和处理SQL文件
            for line in f.readlines():
                # 如果是配置数据库的SQL语句，就去掉末尾的换行
                if line.startswith('SET'):
                    sql_list.append(line.replace('\n', ''))
                # 如果是删除表的语句，则改成删除表中的数据
                elif line.startswith('DROP'):
                    sql_list.append(line.replace('DROP', 'TRUNCATE').replace(' IF EXISTS', '').replace('\n', ''))
                # 如果是插入语句，也删除末尾的换行
                elif line.startswith('INSERT'):
                    sql_list.append(line.replace('\n', ''))
                elif line.startswith('DELETE'):
                    sql_list.append(line.replace('\n', ''))
                # 如果是其他语句，就忽略
                else:
                    pass
        return sql_list

    # 初始化mysql配置
    def init_mysql(self):
        # 读取配置文件
        with open(file="./lib/conf/conf.yaml", mode='r', encoding="utf-8") as file:
            self.conf = yaml.load(stream=file, Loader=yaml.FullLoader)['mysql']

        self.sqlpath = self.conf['sqlpath']
        self.conf.pop('sqlpath')
        # 创建连接，执行语句的时候是在这个连接上执行
        connect = pymysql.connect(**self.conf)

        # 获取游标
        cursor = connect.cursor()
        logger.info("正在恢复%s数据库" % self.sqlpath)
        # 一行一行执行SQL语句
        for sql in self.__read_sql_file(self.sqlpath):
            cursor.execute(sql)
            connect.commit()
        # 关闭游标和连接
        cursor.close()
        connect.close()


# 调试代码
if __name__ == '__main__':
    mysql = Mysql()
    mysql.init_mysql()